﻿module Octopus.OctopusService.Database.VirtualMachines

open System.Data
open System.Data.SqlClient
open System.Data.SqlTypes
open System.Threading
open Octopus.OctopusLib
open Octopus.OctopusService.Database.Core



let private getVirtualMachines (whereCondition:string) =
    let queryBase = "SELECT [VirtualMachinesActive].[ID] as ID1, [VirtualMachinesData].[ID] as ID2, [VirtualMachinesActive].[OctopusGuid] as VmOctopusGuid, * FROM [OctopusDb].[dbo].[VirtualMachinesData] INNER JOIN [OctopusDb].[dbo].[VirtualMachinesActive] ON [VirtualMachinesData].[ID] = [VirtualMachinesActive].[VmDataID]"
    let query = if whereCondition = null then queryBase else sprintf "%s WHERE (%s)" queryBase whereCondition

    use conn = connectoToOctopusDb()
    use cmd = new SqlCommand(query, conn)
    use reader = cmd.ExecuteReader()
    try
      [| while reader.Read() do
           let octopusGuid = reader.GetGuid( reader.GetOrdinal("VmOctopusGuid") )
           let hostname = reader.GetString( reader.GetOrdinal("MachineName") )
           let cpucount = uint16( reader.GetInt32( reader.GetOrdinal("CpuCount") ) )
           let memorysize = uint64( reader.GetInt64( reader.GetOrdinal("MemorySize") ) )
           let ownername = reader.GetString( reader.GetOrdinal("OwnerName") )
           let macaddress = reader.GetString( reader.GetOrdinal("MacAddress") )
           let templateid = reader.GetInt32( reader.GetOrdinal("VmTemplateID") )
           let octopusstate = enum<OctopusVmState>( reader.GetInt32( reader.GetOrdinal("OctopusState") ) )

           let hypervGuid = reader.GetGuid( reader.GetOrdinal("HyperVGuid") )
           let storedpath = reader.GetString( reader.GetOrdinal("StoredPath") )
           let currentserver = reader.GetString( reader.GetOrdinal("CurrentServer") )   
           let hypervstate = enum<HyperVState>( reader.GetInt32( reader.GetOrdinal("HyperVState") ) )
           let healthstate = enum<HyperVHealthState>( reader.GetInt32( reader.GetOrdinal("HyperVHealthState") ) )
           let heartbeat = enum<HyperVHeartbeat>( reader.GetInt32( reader.GetOrdinal("HyperVHeartbeat") ) )
           let hpcstate = reader.GetInt32( reader.GetOrdinal("HpcState") )
           let laststatecheck = reader.GetDateTime( reader.GetOrdinal("LastStateCheck") )
           let ischeckpointed = reader.GetBoolean( reader.GetOrdinal("IsCheckpointed") )
           let ipv4address = System.Net.IPAddress.Parse( reader.GetString( reader.GetOrdinal("Ipv4Address") ) )

           let vm = new VirtualMachine(octopusGuid, hypervGuid, cpucount, memorysize, currentserver, storedpath, hypervstate, healthstate, heartbeat, octopusstate, hpcstate, laststatecheck, ischeckpointed, ownername, hostname, macaddress, ipv4address, templateid)
           yield vm |]
    finally
      reader.Close()
      conn.Close()


let private getVirtualMachinesInPendingCreation whereCondition =
    let queryBase = "SELECT * FROM [OctopusDb].[dbo].[VirtualMachinesData] INNER JOIN [OctopusDb].[dbo].[VirtualMachinesPendingCreation] ON [VirtualMachinesData].[ID] = [VirtualMachinesPendingCreation].[VmDataID]"
    let query = if whereCondition = null then queryBase else sprintf "%s WHERE (%s)" queryBase whereCondition

    use conn = connectoToOctopusDb()
    use cmd = new SqlCommand(query, conn)
    use reader = cmd.ExecuteReader()
    try
      [| while reader.Read() do
           let id = reader.GetInt32(0)
           let vmId = reader.GetInt32( reader.GetOrdinal("VmDataID") )
           if id <> vmId then failwith "Error reading the virtual machines tables"
           
           let octopusGuid = reader.GetGuid( reader.GetOrdinal("OctopusGuid") )
           let cpucount = uint16( reader.GetInt32( reader.GetOrdinal("CpuCount") ) )
           let memorysize = uint64( reader.GetInt64( reader.GetOrdinal("MemorySize") ) )
           let ownername = reader.GetString( reader.GetOrdinal("OwnerName") )
           let hostname = reader.GetString( reader.GetOrdinal("MachineName") )
           let macaddress = reader.GetString( reader.GetOrdinal("MacAddress") )
           let templateid = reader.GetInt32( reader.GetOrdinal("VmTemplateID") )

           let hostServer = reader.GetString( reader.GetOrdinal("HostServer") )   
           let password = reader.GetString( reader.GetOrdinal("Password") )   

           let vm = new VirtualMachinePendingCreation(octopusGuid, cpucount, memorysize, OctopusVmState.ScheduledForCreation, ownername, hostname, macaddress, templateid, hostServer, password)
           yield vm |]
    finally
      reader.Close()
      conn.Close()


let private getVirtualMachinesRequested whereCondition =
    let queryBase = "SELECT [ID],[OctopusGuid],[NumberOfVm],[OwnerName],[MachineNames],[Password],[Description],[CpuCount],[MemorySize],[VmTemplateID] FROM [OctopusDb].[dbo].[VirtualMachinesRequested]"
    let query = if whereCondition = null then queryBase else sprintf "%s WHERE (%s)" queryBase whereCondition

    use conn = connectoToOctopusDb()
    use cmd = new SqlCommand(query, conn)
    use reader = cmd.ExecuteReader()
    try
      [| while reader.Read() do
           let octopusGuid = reader.GetGuid( reader.GetOrdinal("OctopusGuid") )
           let numberOfVm = uint16( reader.GetInt32( reader.GetOrdinal("NumberOfVm") ) )
           let ownername = reader.GetString( reader.GetOrdinal("OwnerName") )
           let hostname = reader.GetString( reader.GetOrdinal("MachineNames") )
           let password = reader.GetString( reader.GetOrdinal("Password") )
           let cpuCount = uint16( reader.GetInt32( reader.GetOrdinal("CpuCount") ) )
           let memorySize = uint64( reader.GetInt64( reader.GetOrdinal("MemorySize") ) )
           let vmTemplateID = reader.GetInt32( reader.GetOrdinal("VmTemplateID") )

           let vm = new VirtualMachineRequested(octopusGuid, numberOfVm, ownername, hostname, cpuCount, memorySize, vmTemplateID, password)
           yield vm |]
    finally
      reader.Close()
      conn.Close()

//----------------------------------

let getAllVm () =
    getVirtualMachines null

let getVmByServer server =
    let cond = sprintf "[CurrentServer]='%s'" server
    getVirtualMachines cond

let getVmByOwner ownerName =
    let cond = sprintf "[OwnerName]='%s'" ownerName
    getVirtualMachines cond  

let getAllVmInConfigurationState () =
    let cond = sprintf "[OctopusState]='%d' OR [OctopusState]='%d'" (int OctopusVmState.ScheduledForConfiguring) (int OctopusVmState.Configuring)
    getVirtualMachines cond  

let getVmByOctopusGuid (octopusGuid:System.Guid) =
    let cond = sprintf "[VirtualMachinesData].[OctopusGuid]='%s'" (string octopusGuid)
    let vm = getVirtualMachines cond
    if vm.Length = 0 then failwith "No virtual machines found"
    else vm.[0]

let getVmByHyperVGuid (hypervGuid:System.Guid) =
    let cond = sprintf "[HyperVGuid]='%s'" (string hypervGuid)
    let vm = getVirtualMachines cond
    if vm.Length = 0 then failwith "No virtual machines found"
    else vm.[0]

let getAllVmInPendingCreation () =
    getVirtualMachinesInPendingCreation null

let getVmInPendingCreationByOwner ownerName =
    let cond = sprintf "[OwnerName]='%s'" ownerName
    getVirtualMachinesInPendingCreation cond

let getVmRequestedByGuid (octopusGuid:System.Guid) =
    let cond = sprintf "[OctopusGuid]='%s'" (string octopusGuid)
    let vmReq = getVirtualMachinesRequested cond
    if vmReq.Length = 0 then failwith "No virtual machines found"
    else vmReq.[0]


//----------------------------------

let deleteVirtualMachine (octopusGuid:System.Guid) =
    use conn = connectoToOctopusDb()
    use trans = conn.BeginTransaction(IsolationLevel.Serializable)
    let cmd = new SqlCommand(Connection=conn, Transaction=trans)    
    try
      cmd.CommandText <- sprintf "DELETE FROM [OctopusDb].[dbo].[VirtualMachinesActive] WHERE [OctopusGuid]='%s'" (string octopusGuid)
      cmd.ExecuteNonQuery() |> ignore
      cmd.CommandText <- sprintf "UPDATE [OctopusDb].[dbo].[VirtualMachinesData] SET [OctopusState]='%d' WHERE [OctopusGuid]='%s'" (int OctopusVmState.Deleted) (string octopusGuid)
      cmd.ExecuteNonQuery() |> ignore
      trans.Commit()
      conn.Close()
    with _ ->
      trans.Rollback()
      conn.Close()
      reraise()

//----------------------------------


let insertVirtualMachinesRequested (vmr:VirtualMachineRequested) =
    use conn = connectoToOctopusDb()
    use trans = conn.BeginTransaction(IsolationLevel.Serializable)
    let cmd = new SqlCommand(Connection=conn, Transaction=trans)    
    try
      cmd.CommandText <- "INSERT INTO [OctopusDb].[dbo].[VirtualMachinesRequested]" +
                         " ([OctopusGuid],[NumberOfVm],[OwnerName],[MachineNames],[Password],[Description],[CpuCount],[MemorySize],[VmTemplateID])" +
                         " VALUES (" +
                         (sprintf "'%s','%d','%s','%s','%s','%s','%d','%d','%d'"
                           (string vmr.OctopusGuid) vmr.NumberOfVm vmr.OwnerName vmr.HostName vmr.Password "" vmr.CpuCount vmr.MemorySize vmr.VmTemplateID) +
                         "); SELECT SCOPE_IDENTITY()"
      let vmDataId = cmd.ExecuteScalar() :?> System.Decimal

      trans.Commit()
      conn.Close()
    with _ ->
      trans.Rollback()
      conn.Close()
      reraise()


let denyVMRequest (vmrGuid:System.Guid) =
    use conn = connectoToOctopusDb()
    let query = sprintf "UPDATE [OctopusDb].[dbo].[VirtualMachinesRequested] SET [Allowed]='FALSE' WHERE [OctopusGuid]='%s'" (string vmrGuid)
    try
      use cmd = new SqlCommand(query, conn, CommandTimeout=10)
      let n = cmd.ExecuteNonQuery()
      if n = 0 then failwithf "VM Request %s not found" (string vmrGuid)
    finally conn.Close()

let moveVMRequestInPendingCreation (vmr:VirtualMachineRequested) (onHost:string array) =
    use conn = connectoToOctopusDb()
    use trans = conn.BeginTransaction(IsolationLevel.Serializable)
    let cmd = new SqlCommand(Connection=conn, Transaction=trans)    
    try
        cmd.CommandText <- sprintf "UPDATE [OctopusDb].[dbo].[VirtualMachinesRequested] SET [Allowed]='TRUE' WHERE [OctopusGuid]='%s'" (string vmr.OctopusGuid)
        cmd.ExecuteNonQuery() |> ignore

        let hostNames = Octopus.OctopusService.General.parseMultipleHostname vmr.HostName
        if hostNames.Length <> onHost.Length then
            failwith "The hostname provided doesn't match the number of virtual machine specified"

        for i = 0 to (hostNames.Length - 1) do
            let hostServer = onHost.[i]
            let hostName = hostNames.[i]

            let newGuid = System.Guid.NewGuid()
            cmd.CommandText <- "INSERT INTO [OctopusDb].[dbo].[VirtualMachinesData]" +
                                " ([OctopusGuid],[Description],[CpuCount],[MemorySize],[OctopusState],[OwnerName],[MachineName],[MacAddress],[VmTemplateID])" +
                                " VALUES (" +
                                (sprintf "'%s','%s','%d','%d','%d','%s','%s','%s','%d'"
                                    (string newGuid) "" vmr.CpuCount vmr.MemorySize (int OctopusVmState.ScheduledForCreation) vmr.OwnerName hostName "" vmr.VmTemplateID) +
                                "); SELECT SCOPE_IDENTITY()"
            let vmDataId = cmd.ExecuteScalar() :?> System.Decimal

            //let timeNow = System.DateTime.Now.ToString("yyyy/MM/dd hh:mm:ss")
            cmd.CommandText <- "INSERT INTO [OctopusDb].[dbo].[VirtualMachinesPendingCreation]" +
                                " ([VmDataID],[HostServer],[Password]) VALUES (" +
                                (sprintf "'%d','%s','%s'" (int vmDataId) hostServer vmr.Password) + ")"
            cmd.ExecuteNonQuery() |> ignore

        trans.Commit()
        conn.Close()
    with _ ->
        trans.Rollback()
        conn.Close()
        reraise()


//----------------------------------

let updateVirtualMachineServer (octopusGuid:System.Guid) (currentServer:string) =
    use conn = connectoToOctopusDb()
    let query = sprintf "UPDATE [OctopusDb].[dbo].[VirtualMachinesActive] SET [CurrentServer]='%s' WHERE [OctopusGuid]='%s'" currentServer (string octopusGuid)
    try
      use cmd = new SqlCommand(query, conn, CommandTimeout=10)
      let n = cmd.ExecuteNonQuery()
      if n = 0 then failwithf "Virtual machine %s not found" (string octopusGuid)
    finally conn.Close()

let updateVirtualMachineIpv4Address (octopusGuid:System.Guid) (newIp:System.Net.IPAddress) =
    let ip = string newIp
    use conn = connectoToOctopusDb()
    let query = sprintf "UPDATE [OctopusDb].[dbo].[VirtualMachinesActive] SET [Ipv4Address]='%s' WHERE [OctopusGuid]='%s'" ip (string octopusGuid)
    try
      use cmd = new SqlCommand(query, conn, CommandTimeout=10)
      let n = cmd.ExecuteNonQuery()
      if n = 0 then failwithf "Virtual machine %s not found" (string octopusGuid)
    finally conn.Close()

let updateVirtualMachineMacAddress (octopusGuid:System.Guid) (newMac:string) =
    use conn = connectoToOctopusDb()
    let query = sprintf "UPDATE [OctopusDb].[dbo].[VirtualMachinesData] SET [MacAddress]='%s' WHERE [OctopusGuid]='%s'" newMac (string octopusGuid)
    try
      use cmd = new SqlCommand(query, conn, CommandTimeout=10)
      let n = cmd.ExecuteNonQuery()
      if n = 0 then failwithf "Virtual machine %s not found" (string octopusGuid)
    finally conn.Close()


let updateVirtualMachineHyperVStates (octopusGuid:System.Guid) (state:HyperVState option) (healthstate:HyperVHealthState option) (heartbeat:HyperVHeartbeat option) =
    match state, healthstate, heartbeat with
    | None,None,None -> raise (new System.ArgumentException("You must specify at least one state to update"))
    | _ -> ()

    let sb = new System.Text.StringBuilder()
    sb.Append("UPDATE [OctopusDb].[dbo].[VirtualMachinesActive] SET ") |> ignore
    if state.IsSome then sb.Append("[HyperVState]='").Append(int state.Value).Append("', ") |> ignore
    if healthstate.IsSome then sb.Append("[HyperVHealthState]='").Append(int healthstate.Value).Append("', ") |> ignore
    if heartbeat.IsSome then sb.Append("[HyperVHeartbeat]='").Append(int heartbeat.Value).Append("', ") |> ignore
  
    let timeStr = System.DateTime.Now.ToString("yyyy/MM/dd hh:mm:ss")
    let tmp = sprintf "[LastStateCheck]='%s' WHERE [OctopusGuid]='%s'" timeStr (string octopusGuid)
    sb.Append(tmp) |> ignore

    use conn = connectoToOctopusDb()
    let query = sb.ToString()

    try
      use cmd = new SqlCommand(query, conn, CommandTimeout=10)
      let n = cmd.ExecuteNonQuery()
      if n = 0 then failwithf "Virtual machine %s not found" (string octopusGuid)
    finally
      conn.Close()

let updateVirtualMachineHyperVStates2 (octopusGuid:System.Guid) (state:HyperF.VMState option) (healthstate:HyperF.VMHealthState option) (heartbeat:HyperF.VMHeartbeat option) =
    let s1 = match state with Some(s) -> Some(enum<HyperVState> (int s)) | None -> None
    let s2 = match healthstate with Some(s) -> Some(enum<HyperVHealthState> (int s)) | None -> None
    let s3 = match heartbeat with Some(s) -> Some(enum<HyperVHeartbeat> (int s)) | None -> None
    updateVirtualMachineHyperVStates octopusGuid s1 s2 s3

let updateVirtualMachineOctopusState (octopusGuid:System.Guid) (octopusState:OctopusVmState) =
    use conn = connectoToOctopusDb()
    let query = sprintf "UPDATE [OctopusDb].[dbo].[VirtualMachinesData] SET [OctopusState]='%d' WHERE [OctopusGuid]='%s'" (int octopusState) (string octopusGuid)

    try
      use cmd = new SqlCommand(query, conn, CommandTimeout=10)
      let n = cmd.ExecuteNonQuery()
      if n = 0 then failwithf "Virtual machine %s not found" (string octopusGuid)
    finally
     conn.Close()

let updateVirtualMachineCheckpointState (octopusGuid:System.Guid) (isCheckpointed:bool) =
    use conn = connectoToOctopusDb()
    let query = sprintf "UPDATE [OctopusDb].[dbo].[VirtualMachinesActive] SET [IsCheckpointed]='%s' WHERE [OctopusGuid]='%s'" (string isCheckpointed) (string octopusGuid)

    try
      use cmd = new SqlCommand(query, conn, CommandTimeout=10)
      let n = cmd.ExecuteNonQuery()
      if n = 0 then failwithf "Virtual machine %s not found" (string octopusGuid)
    finally
      conn.Close()

let updateVirtualMachineTemplateId (octopusGuid:System.Guid) (templateId:int) =
    use conn = connectoToOctopusDb()
    let query = sprintf "UPDATE [OctopusDb].[dbo].[VirtualMachinesData] SET [VmTemplateID]='%d' WHERE [OctopusGuid]='%s'" templateId (string octopusGuid)

    try
      use cmd = new SqlCommand(query, conn, CommandTimeout=10)
      let n = cmd.ExecuteNonQuery()
      if n = 0 then failwithf "Virtual machine %s not found" (string octopusGuid)
    finally
      conn.Close()



//--------------------------------


let movePendingVmToActive (vmp:VirtualMachinePendingCreation) (vm:VirtualMachine) =
    if not( vmp.OctopusGuid.Equals(vm.OctopusGuid) ) then
      failwith "the OctopusGuid in vmp and vm don't match"

    use conn = connectoToOctopusDb()
    use trans = conn.BeginTransaction(IsolationLevel.Serializable)
    let cmd = new SqlCommand(Connection=conn, Transaction=trans)    
  
    try
      cmd.CommandText <- sprintf "SELECT [ID] FROM [OctopusDb].[dbo].[VirtualMachinesData] WHERE [OctopusGuid]='%s'" (string vmp.OctopusGuid)
      let id =
        use reader = cmd.ExecuteReader()
        if reader.Read() then reader.GetInt32(0)
        else failwith "VirtualMachineData not found"

      let timeStr = vm.LastStatesCheck.ToString("yyyy/MM/dd hh:mm:ss")
      cmd.CommandText <- "INSERT INTO [OctopusDb].[dbo].[VirtualMachinesActive]" +
                         " ([VmDataID],[OctopusGuid],[HyperVGuid],[CurrentServer],[StoredPath],[HyperVState],[HyperVHealthState],[HyperVHeartbeat],[HpcState],[LastStateCheck],[IsCheckpointed],[Ipv4Address],[Ipv6Address])" +
                         " VALUES (" +
                         (sprintf "'%d','%s','%s','%s','%s','%d','%d','%d','%d','%s','%s','%s',NULL"
                           (int id) (string vm.OctopusGuid) (string vm.HyperVGuid) vm.CurrentServer vm.StoredPath (int vm.HyperVState) (int vm.HyperVHealthState) (int vm.HyperVHeartbeat) vm.HpcState timeStr (string vm.IsCheckpointed) (vm.Ipv4Address.ToString())) +
                         ")"
      cmd.ExecuteNonQuery() |> ignore
      
      cmd.CommandText <- sprintf "UPDATE [OctopusDb].[dbo].[VirtualMachinesData] SET [OctopusState]='%d' WHERE [OctopusGuid]='%s'" (int vm.OctopusState) (string vm.OctopusGuid)
      cmd.ExecuteNonQuery() |> ignore

      cmd.CommandText <- sprintf "DELETE FROM [OctopusDb].[dbo].[VirtualMachinesPendingCreation] WHERE [VmDataID]='%d'" (int id)
      cmd.ExecuteNonQuery() |> ignore
      
      trans.Commit()
      conn.Close()
    with _ ->
      trans.Rollback()
      conn.Close()
      reraise()


let moveVmFrom_ScheduledForConfiguring_To_Configuring_State (vm:VirtualMachine) =
    use conn = connectoToOctopusDb()
    use trans = conn.BeginTransaction(IsolationLevel.Serializable)
    let cmd = new SqlCommand(Connection=conn, Transaction=trans)    
  
    try
      cmd.CommandText <- sprintf "SELECT [OctopusState] FROM [OctopusDb].[dbo].[VirtualMachinesData] WHERE [OctopusGuid]='%s'" (string vm.OctopusGuid)
      let num =
        use reader = cmd.ExecuteReader()
        if reader.Read() then reader.GetInt32(0)
        else failwith "VirtualMachineData not found"

      let octopusState = enum<OctopusVmState> num
      if octopusState <> OctopusVmState.ScheduledForConfiguring then
        failwith "The virtual machine is not in ScheduledForConfiguring state"

      cmd.CommandText <- sprintf "UPDATE [OctopusDb].[dbo].[VirtualMachinesData] SET [OctopusState]='%d' WHERE [OctopusGuid]='%s'" (int OctopusVmState.Configuring) (string vm.OctopusGuid)
      cmd.ExecuteNonQuery() |> ignore

      trans.Commit()
      conn.Close()
    with _ ->
      trans.Rollback()
      conn.Close()
      reraise()


let moveVmFrom_To_Normal_State (vm:VirtualMachine) =
    use conn = connectoToOctopusDb()
    use trans = conn.BeginTransaction(IsolationLevel.Serializable)
    let cmd = new SqlCommand(Connection=conn, Transaction=trans)    
  
    try
      cmd.CommandText <- sprintf "SELECT [OctopusState] FROM [OctopusDb].[dbo].[VirtualMachinesData] WHERE [OctopusGuid]='%s'" (string vm.OctopusGuid)
      let num =
        use reader = cmd.ExecuteReader()
        if reader.Read() then reader.GetInt32(0)
        else failwith "VirtualMachineData not found"

      let octopusState = enum<OctopusVmState> num
      if octopusState <> OctopusVmState.Configuring then
        failwith "The virtual machine is not in Configuring state"

      cmd.CommandText <- sprintf "UPDATE [OctopusDb].[dbo].[VirtualMachinesData] SET [OctopusState]='%d' WHERE [OctopusGuid]='%s'" (int OctopusVmState.Normal) (string vm.OctopusGuid)
      cmd.ExecuteNonQuery() |> ignore

      trans.Commit()
      conn.Close()
    with _ ->
      trans.Rollback()
      conn.Close()
      reraise()